package com.kcsj.dao.User;


import com.kcsj.domain.user.Resource;
import com.kcsj.util.JdbcHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.LinkedList;
/**
 * @author 胡庆阳
 * 29th Nov. 2020
 */
public class ResourceDao {
    private static Collection<Integer> authorityId = new ArrayList<>();
    private static final ResourceDao RESOURCES_DAO =
            new ResourceDao();

    private ResourceDao() {

    }

    public static ResourceDao getInstance() {
        return RESOURCES_DAO;
    }

    //查询所有Resource
    public Collection<Resource> findAll(Connection conn) throws SQLException {
        Collection<Resource> resources = new LinkedList<>();
        //预编译方式进行查询
        String s = "select * from resource";
        PreparedStatement preparedStatement = conn.prepareStatement(s);
        ResultSet resultSet = preparedStatement.executeQuery();
        //将查询到的数据转为degree对象
        while (resultSet.next()) {
            int id = resultSet.getInt(1);
            int pid = resultSet.getInt(2);
            String type = resultSet.getString(3);
            String url = resultSet.getString(4);
            String description = resultSet.getString(5);
            String icon = resultSet.getString(6);
            String remarks = resultSet.getString(8);
            //获取该权限可以访问那些URL
            if (pid == 0) {
                Collection<Resource> child = this.findChild(id,conn);
                Resource resource = new Resource(id, pid,type, url, description, icon,remarks,child);
                resources.add(resource);
            }
        }
        //关闭连接
        JdbcHelper.close(resultSet, preparedStatement);
        return resources;
    }
    //查询子Resource
    private Collection<Resource> findChild (Integer pid,Connection conn) throws SQLException {
        Collection<Resource> resources = new LinkedList<>();
        //预编译方式进行查询
        String s = "select * from resource where  pid = ?";
        PreparedStatement preparedStatement = conn.prepareStatement(s);
        preparedStatement.setInt(1, pid);
        ResultSet resultSet = preparedStatement.executeQuery();
        //将查询到的数据转为degree对象
        while (resultSet.next()) {
            int id = resultSet.getInt(1);
            String type = resultSet.getString(3);
            String url = resultSet.getString(4);
            String description = resultSet.getString(5);
            String icon = resultSet.getString(6);
            String remarks = resultSet.getString(8);
            //获取该权限可以访问那些URL
            Collection<Resource> child = findChild(id, conn);
            Resource resource = new Resource(id, pid,type,url,description,icon,remarks,child);
            resources.add(resource);
        }
        //关闭连接
        JdbcHelper.close(resultSet, preparedStatement);
        return resources;
    }
    //查询菜单
    public Resource findMenu(Integer role_id,Integer id,Connection conn) throws SQLException {
        Resource resource = null;
        //预编译方式进行查询
        String s = "select * from resource where id = ? and type = ?";
        PreparedStatement preparedStatement = conn.prepareStatement(s);
        preparedStatement.setInt(1, id);
        preparedStatement.setString(2, "menu");
        ResultSet resultSet = preparedStatement.executeQuery();
        //将查询到的数据转为degree对象
        if (resultSet.next()) {
            int pid = resultSet.getInt(2);
            String type = resultSet.getString(3);
            String url = resultSet.getString(4);
            String description = resultSet.getString(5);
            String icon = resultSet.getString(6);
            String remarks = resultSet.getString(8);
            //获取该权限可以访问那些URL
            if (pid == 0) {
                Collection<Resource> child = this.findChildMenu(role_id,id,conn);
                Collection<String> authority = this.findAuthority(role_id,id,conn);
                resource = new Resource(id, pid,type, url, description,icon, remarks,authority,child);
            }
        }
        //关闭连接
        JdbcHelper.close(resultSet, preparedStatement);
        return resource;
    }
    //查询子菜单
    private Collection<Resource> findChildMenu (Integer role_id,Integer pid,Connection conn) throws SQLException {
        Collection<Resource> resources = new LinkedList<>();
        //预编译方式进行查询
        String s = "select * from resource where  pid = ? and type = ?";
        PreparedStatement preparedStatement = conn.prepareStatement(s);
        preparedStatement.setInt(1, pid);
        preparedStatement.setString(2, "menu");
        ResultSet resultSet = preparedStatement.executeQuery();
        //将查询到的数据转为degree对象
        while (resultSet.next()) {
            int id = resultSet.getInt(1);
            String type = resultSet.getString(3);
            String url = resultSet.getString(4);
            String description = resultSet.getString(5);
            String icon = resultSet.getString(6);
            String remarks = resultSet.getString(8);
            //获取该权限可以访问那些URL
            Collection<String> authority = this.findAuthority(role_id,id, conn);
            Collection<Resource> child = this.findChildMenu(role_id,id, conn);
            Resource resource = new Resource(id, pid,type,url,description,icon,remarks,authority,child);
            boolean byRoleAndResource = RoleAssResourceDao.getInstance().findByRoleAndResource(role_id, id,conn);
            if(byRoleAndResource){
                resources.add(resource);
            }
        }
        //关闭连接
        JdbcHelper.close(resultSet, preparedStatement);
        return resources;
    }
    //查询权限
    private Collection<String> findAuthority (Integer role_id,Integer pid,Connection conn) throws SQLException {
        Collection<String> authorities = new LinkedList<>();
        //预编译方式进行查询
        String s = "select * from resource where  pid = ? and type = ?";
        PreparedStatement preparedStatement = conn.prepareStatement(s);
        preparedStatement.setInt(1, pid);
        preparedStatement.setString(2, "authority");
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            String operating = resultSet.getString("operating");
            int id = resultSet.getInt("id");
            boolean byRoleAndResource = RoleAssResourceDao.getInstance().findByRoleAndResource(role_id, id,conn);
            if(byRoleAndResource){
                authorities.add(operating);
            }
        }
        //关闭连接
        JdbcHelper.close(resultSet, preparedStatement);
        return authorities;
    }
    //查询是否有孩子
    public Boolean hasChild (Integer pid,Connection conn) throws SQLException {
        boolean has = false;
        //预编译方式进行查询
        String s = "select * from resource where  pid = ?";
        PreparedStatement preparedStatement = conn.prepareStatement(s);
        preparedStatement.setInt(1, pid);
        ResultSet resultSet = preparedStatement.executeQuery();
        //将查询到的数据转为degree对象
        if (resultSet.next()) {
            has = true;
        }
        //关闭连接
        JdbcHelper.close(resultSet, preparedStatement);
        return has;
    }

    //修改Resource
    public boolean update(Resource resource, Connection conn) throws SQLException {
        //预编译方式进行修改数据
        String s = "update resource set pid = ?,type = ? ,url = ?,description = ?,icon = ?,operating = ?,remarks = ? where id = ?";
        PreparedStatement preparedStatement = conn.prepareStatement(s);
        preparedStatement.setInt(1,resource.getPid());
        preparedStatement.setString(2,resource.getType());
        preparedStatement.setString(3,resource.getUrl());
        preparedStatement.setString(4,resource.getDescription());
        preparedStatement.setString(5,resource.getIcon());
        preparedStatement.setString(6,resource.getOperating());
        preparedStatement.setString(7,resource.getRemarks());
        preparedStatement.setInt(8,resource.getId());
        //如果执行影响行数大于0，则修改成功
        int i = preparedStatement.executeUpdate();
        //关闭连接
        JdbcHelper.close(preparedStatement);
        return i > 0;
    }

    //向Resource表中添加数据
    public int add(Resource resource, Connection conn) throws SQLException {
        //预编译方式进行增加数据
        String s = "insert into resource(pid,type,url,description,icon,operating,remarks) value (?,?,?,?,?,?,?)";
        PreparedStatement preparedStatement = conn.prepareStatement(s);
        preparedStatement.setInt(1,resource.getPid());
        preparedStatement.setString(2,resource.getType());
        preparedStatement.setString(3,resource.getUrl());
        preparedStatement.setString(4,resource.getDescription());
        preparedStatement.setString(5,resource.getIcon());
        preparedStatement.setString(6,resource.getOperating());
        preparedStatement.setString(7,resource.getRemarks());
        //如果执行影响行数大于0，则增加成功
        int id = preparedStatement.executeUpdate();
        //关闭连接
        JdbcHelper.close(preparedStatement);
        return id;
    }

    //在Resource表根据id删除相应数据
    public boolean delete(Integer id, Connection conn) throws SQLException {
        //预编译方式进行删除数据
        String s = "delete from resource where id = ?";
        PreparedStatement preparedStatement = conn.prepareStatement(s);
        preparedStatement.setInt(1,id);
        //如果执行影响行数大于0，则增加成功
        int i = preparedStatement.executeUpdate();
        //关闭连接
        JdbcHelper.close(preparedStatement);
        return i > 0;
    }
}
